#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi


/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
-- 每天/每月/每年线上线下以及新老学员的意向用户个数
insert into zx_rpt.rpt_intention_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'all';

-- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
insert into zx_rpt.rpt_intention_area_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    area,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'area';

-- 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
insert into zx_rpt.rpt_intention_subject_TopN
select
    '${dt}' as dt,
    year,
    month,
    day,
    itcast_subject_id,
    subject_name,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'subject'
order by intention_amt desc
limit 10;

-- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
insert into zx_rpt.rpt_intention_school_TopN
select
    '${dt}' as dt,
    year,
    month,
    day,
    itcast_school_id,
    school_name,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'school'
order by intention_amt desc
limit 10;

-- 每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
insert into zx_rpt.rpt_intention_channel_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    origin_channel,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'origin';

-- 每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
insert into zx_rpt.rpt_intention_dept_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    tdepart_id,
    dept_name,
    origin_type,
    clue_state,
    intention_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'dept';

-- 每天线上线下及新老学员的有效线索个数
insert into zx_rpt.rpt_valid_clue_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    origin_type,
    clue_state,
    valid_clue_amt
from
    zx_dm.dm_intention
where
   time_type = 'day' and new_group_type = 'all';

-- 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
insert into zx_rpt.rpt_valid_rate_cnt
select
    '${dt}' as dt,
    year,
    month,
    day,
    hour,
    origin_type,
    clue_state,
    cast(
      cast(valid_clue_amt as DECIMAL(38,4)) / cast(intention_amt as DECIMAL(38,4))
      * 100
      as DECIMAL(5,2)
   ) valid_rate
from
    zx_dm.dm_intention
where
   time_type = 'hour' and new_group_type = 'all';
"